import { HttpException, HttpStatus, Injectable } from "@nestjs/common";
import { InjectRepository } from "@nestjs/typeorm";
import * as ExcelJs from "exceljs";
import { FindManyOptions, Repository } from "typeorm";
import { getTokenUserInfo, handleFindOptions } from "src/utils";
import { CreateClassDto } from "./dto/create-class.dto";
import { UpdateClassDto } from "./dto/update-class.dto";
import { CourseEnum } from "src/enums/school/course.enum";
import { Class } from "./entities/class.entity";
import { Teacher } from "../teacher/entities/teacher.entity";
import type { Request } from "express";
import type { QueryType } from "src/types/index.types";

// ! typeorm QueryBuilder QueryRunner(事务) 方式

@Injectable()
export class ClassService {
  constructor(
    @InjectRepository(Class) private readonly classServer: Repository<Class>,
    @InjectRepository(Teacher) private readonly teacherServer: Repository<Teacher>
  ) {}

  async create(createClassDto: CreateClassDto, request: Request) {
    const { gradeAndClass } = createClassDto;
    const classQB = this.classServer.createQueryBuilder("class");
    // 检查班级是否存在
    const hasGradeAndClass = await classQB.where("class.gradeAndClass = :gradeAndClass", { gradeAndClass }).getCount();
    if (hasGradeAndClass > 0) throw new HttpException("当前班级已存在", HttpStatus.BAD_REQUEST);
    // 初始化班级对象
    const currenClass = new Class();
    // 如果有 token，则获取用户信息
    const token = request.get("authorization");
    if (token) {
      const userInfo = getTokenUserInfo(token);
      currenClass.createBy = userInfo.nickname;
      currenClass.createById = userInfo.id;
    }
    // 获取科目教师 id
    const teacherNames = Object.values(CourseEnum).map(item => item + "TeacherId");
    const teacherIds = teacherNames.filter(key => createClassDto[key]).map(key => createClassDto[key]);
    // 设置班主任
    currenClass.classTeacher = createClassDto.classTeacher;
    currenClass.classTeacherId = createClassDto.classTeacherId;
    // 设置班级基本信息
    currenClass.subject = createClassDto.subject;
    currenClass.gradeAndClass = gradeAndClass;
    // 加载教师信息
    const teacherQB = this.teacherServer.createQueryBuilder("teacher");
    const teachers = await teacherQB.where("teacher.id IN (:teacherIds)", { teacherIds }).getMany();
    // 设置班级的教师
    currenClass.teachers = teachers;
    // 开启事务
    const connection = this.classServer.manager.connection;
    const queryRunner = connection.createQueryRunner();
    await queryRunner.connect();
    await queryRunner.startTransaction();
    try {
      // 在事务中保存班级信息
      await queryRunner.manager.save(currenClass);
      // 提交事务
      await queryRunner.commitTransaction();
    } catch (error) {
      console.log("🚀 ~ create ~ error:", error);
      // 回滚事务
      await queryRunner.rollbackTransaction();
      throw new HttpException("创建班级失败", HttpStatus.INTERNAL_SERVER_ERROR);
    } finally {
      // 释放查询运行器
      await queryRunner.release();
    }
    return currenClass;
  }

  async findAll(query?: QueryType) {
    const options: FindManyOptions<Class> = handleFindOptions(query || {});
    const { skip, take } = options;
    const { classTeacherName, teacherName, gradeAndClass } = query || {};
    // 使用QueryBuilder来构建查询，包括排序和分页

    // 主查询使用子查询的结果来获取班级及其所有教师
    const queryBuilder = await this.classServer
      .createQueryBuilder("class")
      .leftJoinAndSelect("class.teachers", "teacher") // 连接教师表
      .leftJoinAndSelect("class.course", "course") // 连接课程表
      .addSelect(`CAST(SUBSTRING_INDEX(class.gradeAndClass, '-', 1) AS UNSIGNED)`, "gradeName") // 年级部分排序
      .addSelect(
        `CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(class.gradeAndClass, '-', -1), '-', 1) AS UNSIGNED)`,
        "className"
      ) // 班级部分排序
      .orderBy("gradeName", "ASC")
      .addOrderBy("className", "ASC");
    // 查询班主任
    if (classTeacherName) {
      queryBuilder.andWhere("class.classTeacher LIKE :classTeacher", { classTeacher: `%${classTeacherName}%` });
    }
    // 查询任课老师
    if (teacherName) {
      // 子查询找到包含特定教师名字的所有班级 ID
      const subQuery = await this.teacherServer
        .createQueryBuilder("teacher")
        .innerJoin("class_teacher", "ct", "teacher.id = ct.teacherId") // 显式连接中间表
        .select("ct.classId", "classId")
        .where("teacher.teacherName LIKE :searchKeyword", { searchKeyword: `%${teacherName}%` })
        .getRawMany();
      if (subQuery.length === 0) return { rows: [], total: 0 };
      const classIds = subQuery.map(item => item.classId);
      queryBuilder.andWhere(`class.id IN (:...ids)`, { ids: classIds });
    }
    // 查询班级
    if (gradeAndClass) {
      queryBuilder.andWhere("class.gradeAndClass = :gradeAndClass", { gradeAndClass: `${gradeAndClass}` });
    }
    const [result, total] = await queryBuilder
      .skip(skip) // 页数
      .take(take) // 页码
      .getManyAndCount();
    const processedResult = result.map(cls => ({
      ...cls,
      teachers: cls.teachers?.map(teacher => {
        const { id: value, teacherName: label, course: type } = teacher;
        return { value, label, type };
      }),
    }));
    return { rows: processedResult, total };
  }

  findOne(id: number) {
    return `This action returns a #${id} class`;
  }

  async update(id: number, updateClassDto: UpdateClassDto, request: Request) {
    const classQB = this.classServer.createQueryBuilder("class");
    // 检查班级是否存在
    const currentClass = await classQB.where("class.id = :id", { id }).getOne();
    if (!currentClass) throw new HttpException("班级不存在", HttpStatus.BAD_REQUEST);
    // 如果有 token，则获取用户信息
    const token = request.get("authorization");
    if (token) {
      const userInfo = getTokenUserInfo(token);
      currentClass.updateBy = userInfo.nickname;
      currentClass.updateById = userInfo.id;
    }
    // 设置班级基本信息
    currentClass.subject = updateClassDto.subject;
    currentClass.gradeAndClass = updateClassDto.gradeAndClass;
    // 设置班主任
    currentClass.classTeacher = updateClassDto.classTeacher;
    currentClass.classTeacherId = updateClassDto.classTeacherId;
    // 获取教师 ID
    const teacherNames = Object.values(CourseEnum).map(item => `${item}TeacherId`);
    const teacherIds = teacherNames.filter(key => updateClassDto[key]).map(key => updateClassDto[key]);
    // 加载教师信息
    const teacherQB = this.teacherServer.createQueryBuilder("teacher");
    const teachers = await teacherQB.where("teacher.id IN (:teacherIds)", { teacherIds }).getMany();
    currentClass.teachers = teachers;
    // 开启事务
    const connection = this.classServer.manager.connection;
    const queryRunner = connection.createQueryRunner();
    await queryRunner.connect();
    await queryRunner.startTransaction();
    try {
      // 保存班级，这将触发与教师的关系更新
      await queryRunner.manager.save(currentClass);
      // 提交事务
      await queryRunner.commitTransaction();
    } catch (error) {
      console.log("🚀 ~ update ~ error:", error);
      // 回滚事务
      await queryRunner.rollbackTransaction();
      throw new HttpException("更新班级失败", HttpStatus.INTERNAL_SERVER_ERROR);
    } finally {
      // 释放查询运行器
      await queryRunner.release();
    }
    return currentClass;
  }

  async remove(id: number) {
    const result = await this.classServer.createQueryBuilder().delete().from(Class).where("id = :id", { id }).execute();
    if (result.affected === 0) throw new HttpException("班级不存在", HttpStatus.BAD_REQUEST);
    return null;
  }

  async export() {
    const workbook = new ExcelJs.Workbook();
    const worksheet = workbook.addWorksheet("班级表_" + +new Date()); // 设置表名
    // worksheet.properties.defaultColWidth = 30; // 默认宽度
    const { rows } = await this.findAll();
    const getGradeAndClassLabel = (text: `${number}-${number}`) => {
      const [_Grade, _Class] = text.split("-");
      const gradeLabelMap = { 1: "高一", 2: "高二", 3: "高三" };
      const grade: keyof typeof gradeLabelMap = +_Grade as keyof typeof gradeLabelMap;
      return `${gradeLabelMap[grade]}${_Class}班`;
    };
    // 设置列 这样可以设置宽度
    worksheet.columns = [
      { header: "班级", key: "gradeAndClass", width: 14 },
      { header: "文理科", key: "subject", width: 14 },
      { header: "班主任", key: "classTeacher", width: 14 },
      { header: "语文老师", key: "chineseTeacher", width: 14 },
      { header: "数学老师", key: "mathTeacher", width: 14 },
      { header: "英语老师", key: "englishTeacher", width: 14 },
      { header: "政治老师", key: "politicsTeacher", width: 14 },
      { header: "历史老师", key: "historyTeacher", width: 14 },
      { header: "地理老师", key: "geographyTeacher", width: 14 },
      { header: "物理老师", key: "physicsTeacher", width: 14 },
      { header: "化学老师", key: "chemistryTeacher", width: 14 },
      { header: "生物老师", key: "biologyTeacher", width: 14 },
      { header: "体育老师", key: "physicalEducationTeacher", width: 14 },
      { header: "音乐老师", key: "musicTeacher", width: 14 },
      { header: "计算机老师", key: "computerTeacher", width: 14 },
    ];
    const list = rows.map(item => {
      return [
        getGradeAndClassLabel(item.gradeAndClass as `${number}-${number}`),
        item.subject === 0 ? "未分科" : item.subject === 1 ? "文科" : "理科",
        item.classTeacher,
        ...item.teachers.map(item => {
          let teacherName = "";
          worksheet.columns.forEach(el => {
            if (`${item.type}Teacher` === el.key) {
              teacherName = item.label;
            }
          });
          return teacherName;
        }),
      ];
    });
    // 表格数据
    worksheet.addRows(list);
    // 设置第一行样式
    const row1 = worksheet.getRow(1);
    row1.font = { bold: true, size: 14, color: { argb: "4387FB" } };
    // 返回 buffer
    const buffer = await workbook.xlsx.writeBuffer();
    return buffer;
  }
}
